交叉连接可以表A和表B是同一张表取得笛卡尔乘积。
比如说下面这种写法:
SQL
SELECT D.n AS theday, S.n AS shiftno FROM dbo.Nums AS D cross JOIN dbo.Nums AS S WHERE D.n <= 7 AND S.N <= 3 ORDER BY theday, shiftno;
当然也可以表A和表B是两张不同的表,取得笛卡尔乘积。
SQL
SELECT D.n AS theday, S.empid AS shiftno FROM dbo.Nums AS D cross JOIN [HR].[Employees] AS S WHERE D.n <= 7 AND S.empid <= 3 ORDER BY theday, shiftno;
但是CROSS JOIN
不能用ON
条件,只能用WHERE
条件。下面这句与上面的语句查询结果相同。
SQL
SELECT D.n AS theday, S.empid AS shiftno FROM dbo.Nums AS D inner JOIN [HR].[Employees] AS S on D.n <= 7 AND S.empid <= 3 ORDER BY theday, shiftno;
内联接查询,表A和表B中的数据必须紧密对应,不可以是Null。下面的查询中,Production.Products
表中没有商品记录的的日本供货商不会被列出来。INNER
这个关键词是可以舍去的,如果只写JOIN
就表示INNER JOIN
。
SQL
SELECT S.companyname AS supplier, S.country, P.productid, P.productname, P.unitprice FROM Production.Suppliers AS S INNER JOIN Production.Products AS P ON S.supplierid = P.supplierid WHERE S.country = N'Japan';
外连接查询有三种情况:左外连接,右外连接,全外连接。
下面这个查询与上面这个查询写法只差一点点(WHERE变成了AND),但是结果就有区别:
SQL
SELECT S.companyname AS supplier, S.country, P.productid, P.productname, P.unitprice FROM Production.Suppliers AS S INNER JOIN Production.Products AS P ON S.supplierid = P.supplierid AND S.country = N'Japan';
而且Production.Products
表中没有商品记录的的日本供货商同样也会被列出来,但是相关的P.productid, P.productname, P.unitprice都会被记为NULL。
下面这句:
SQL
SELECT E.empid, E.firstname + N' ' + E.lastname AS emp, M.firstname + N' ' + M.lastname AS mgr FROM HR.Employees AS E INNER JOIN HR.Employees AS M ON E.mgrid = M.empid;
用了内联接,则最高主管(CEO)不会被列出来,因为最高主管没有更高的主管了。
而改用左外连接
SQL
SELECT E.empid, E.firstname + N' ' + E.lastname AS emp, M.firstname + N' ' + M.lastname AS mgr FROM HR.Employees AS E LEFT OUTER JOIN HR.Employees AS M ON E.mgrid = M.empid;
则CEO也会被列出来,CEO对应的mgr会被记为NULL。
套用内联接的左外连接:
SQL
SELECT S.companyname AS supplier, S.country, P.productid, P.productname, P.unitprice, C.categoryname FROM Production.Suppliers AS S LEFT OUTER JOIN Production.Products AS P ON S.supplierid = P.supplierid INNER JOIN Production.Categories AS C ON C.categoryid = P.categoryid WHERE S.country = N'Japan';
查询出日本供货商的所有的产品以及产品类别名。而且Production.Products
表中没有商品记录的的日本供货商同样也会被列出来,但是相关的P.productid, P.productname, P.unitprice, C.categoryname都会被记为NULL。
上面的语句与下面带括号的语句等同:
SQL
SELECT S.companyname AS supplier, S.country, P.productid, P.productname, P.unitprice, C.categoryname FROM Production.Suppliers AS S LEFT OUTER JOIN (Production.Products AS P INNER JOIN Production.Categories AS C ON C.categoryid = P.categoryid) ON S.supplierid = P.supplierid WHERE S.country = N'Japan';
RIGHT OUTER JOIN
则与LEFT OUTER JOIN
相反,根据ON条件和WHERE条件查询表A和表B,查询结果可以表A中数据为NULL。FULL OUTER JOIN
则只要表A和表B中任一表中有数据,结果都会被显示出来。无论是表A为NULL,还是表B为NULL。OUTER
也是可以被省略的。LEFT JOIN
就是LEFT OUTER JOIN
的简写,相应的,RIGHT JOIN
是RIGHT OUTER JOIN
的简写,FULL JOIN
是FULL OUTER JOIN
的简写。
**粗体** _斜体_ [链接](http://example.com) `代码` - 列表 > 引用
。你还可以使用@
来通知其他用户。